const models = require('../db')
const express = require('express')
const router = express.Router()
const mysql = require('mysql')
const $sql = require('../sqlMap')
// 连接数据库
const conn = mysql.createConnection(models.mysql)
conn.connect()
const jsonWrite = function (res, ret) {
  if (typeof ret === 'undefined') {
    res.json({
      code: '1', msg: '操作失败'
    })
  } else {
    res.json(
      ret
    )
  }
}
// 接口：增加信息
router.post('/addStu', (req, res) => {
  const sql = $sql.Stu.add
  const params = req.body
  console.log('添加', params)
  conn.query(sql, [params.stu_Id,params.stu_name, params.stu_sex, params.stu_college, params.stu_class], function (err, result) {
    if (err) {
      console.log(err)
    }
    if (result) {
      jsonWrite(res, result)
    }
  })
})

// 接口：查询全部
router.get('/showStu', (req, res) => {
  const sql = $sql.Stu.show
  const params = req.body
  console.log(params)
  conn.query(sql, [params.stu_Id, params.stu_name, params.stu_sex, params.stu_college, params.stu_class], function (err, result) {
    if (err) {
      console.log(err)
    }
    if (result) {
      jsonWrite(res, result)
    }
  })
})

//通过名称查询
router.post('/showStun', (req, res) => {
  const sql = $sql.Stu.showN
  const params = req.body
  console.log('查询',params,sql)
  conn.query(sql, [params.stu_id, params.stu_name, params.stu_sex, params.stu_college, params.stu_class], function (err, result) {
    if (err) {
      console.log(err)
    }
    if (result) {
      jsonWrite(res, result)
    }
  })
  console.log(params.stu_name,sql)
})


// router.post('/showStu', (req, res, next)=> {
//   console.log(req.body)
//   let name = req.body.name
//   let pageSize = req.body.pageSize // 页大小
//   let pageCurrent = req.body.pageCurrent // 当前页
//   let start=(pageCurrent-1)*pageSize; // 起始位置
//   connection.query(`SELECT *  FROM student_info WHERE name LIKE '%${name}%' LIMIT ${start},${pageSize}`,function(err,result){
//     if(err) throw err;
//     let list = result
//     connection.query(`SELECT COUNT(*) AS total FROM student_info`,function(err,result1){
//       if(err) throw err;
//       console.log(result1[0].total)
//       let obj = {
//         pageSize: pageSize,
//         pageCurrent: pageCurrent,
//         total:result1[0].total,
//         list: list
//       }
//       res.send(obj);
//     })
//   })
// });



// 接口：删除信息
router.post('/delStu', (req, res) => {
  const sql = $sql.Stu.del
  const params = req.body
  console.log('删除', params)
  conn.query(sql, [params.stu_id], function (err, result) {
    if (err) {
      console.log(err)
    }
    if (result) {
      jsonWrite(res, result,11)
    }
  })
})
// router.post('/delStu',(req, res, next)=> {
//   let params = req.body.id;
//   if(params){
//     connection.query(`DELETE  FROM  student  WHERE id = ${params} ;`, function(err,result){
//       if(err){
//         res.send("删除失败"+err);
//       }else {
//         console.log(result,'删除')
//         res.send("删除成功");
//       }
//     });
//   }
// })


// 接口：修改信息
router.post('/updateStu', (req, res) => {
  const sql = $sql.Stu.update
  const params = req.body
  console.log('修改', params)
  conn.query(sql, [params.stu_name, params.stu_sex, params.stu_college, params.stu_class, params.stu_id], function (err, result) {
    if (err) {
      console.log(err)
    }
    if (result) {
      jsonWrite(res, result)
    }
  })
})

module.exports = router